<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class M_teachers Extends MY_Model
{
	private $tuid; // teacher user id
	
	public function __construct()
	{
		parent::__construct();
		$this->tuid = $this->get_employee_id();
	}
	
	public function get_employee_id()
	{ 
		$sql = "SELECT e.emp_id
				FROM employees e
				LEFT JOIN users u ON u.id = e.id
				WHERE e.id = ?
				LIMIT 1";
				
		$query = $this->db->query($sql,array($this->c_user->userid));
		if($query->num_rows() >=1)
		{
			return $query->row()->emp_id;
		}else{
			return FALSE;
		}
	}
	
	public function get_classes()
	{
		$classes = $this->get_class_section_room();
		if($classes !== FALSE)
		{
			foreach($classes as $k => $v)
			{
				$processed[$k]['section_name'] = $v->section_name;
				$processed[$k]['section_id'] = $v->id;
				$processed[$k]['room_desc'] = $v->description;
				$processed[$k]['room_name'] = $v->name;
				$processed[$k]['section_studs'] = $this->get_student_for_section($v->id);
				$processed[$k]['class_sched'] = $this->_get_class_schedules($v->id);
			}
			return $processed;
		}else{
			return FALSE;
		}
	}
	
	private function get_class_section_room()
	{
		$sql = 'SELECT s.section_name,
					   s.id,
					   r.description,
					   r.name
				FROM sections s
				LEFT JOIN rooms r ON r.section_id = s.id
				WHERE s.adviser_id = ?
				AND s.year_id = ?';
				
		$query = $this->db->query($sql,array($this->tuid,$this->c_sy->id));
		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	private function get_student_for_section($section_id)
	{
		$sql = 'SELECT e.e_id as eid,
					   p.profile_id as pid,
					   concat_ws("",p.lastname," ",p.firstname,", ",p.middlename) as fullname,
					   p.age,
					   p.dob,
					   gender
				FROM enrollments e
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				WHERE e.e_section_id = ? 
				ORDER by gender, fullname';
				
		$query = $this->db->query($sql,array($section_id));
		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	private function _get_class_schedules($section_id)
	{
		$sql = 'SELECT sched.time_from,
					   sched.time_to,
					   sched.day,
					   su.subj_code,
					   su.subj_desc,
					   concat(e.first_name," ",e.middle_name," ",e.last_name) as teacher
				FROM schedule sched
				LEFT JOIN sections s ON sched.section_id = s.id
				LEFT JOIN subjects su ON su.subj_id = sched.subject_id
				LEFT JOIN employees e ON e.emp_id = sched.teacher_id
				WHERE s.id = ?';
		$query = $this->db->query($sql,array($section_id));
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	
	public function get_schedules()
	{
		$sched = $this->_get_schedules();
		if($sched)
		{
			foreach($sched as $k => $v)
			{
				$time = $v->time_from.' - '.$v->time_to;
				
				$s_id = intval($v->id);
				$su_id = intval($v->subject_id);
				
				$processed[$k]['time'] = $time;
				$processed[$k]['day'] = ucwords($v->day);
				$processed[$k]['section_name'] = ucwords($v->section_name);
				$processed[$k]['room_name'] = ucwords($v->name);
				$processed[$k]['room_desc'] = ucwords($v->description);
				$processed[$k]['level_desc'] = ucwords($v->level_desc);
				$processed[$k]['adviser'] = ucwords($v->adviser);
				$processed[$k]['subj'] = ucwords($v->subj_desc);
				$processed[$k]['studs'] = $this->get_student_for_section($v->id);
				$processed[$k]['link'] = $this->hs->encrypt($s_id,$su_id);
			}
			return $processed;
		}else
		{
			return FALSE;
		}
	}
	
	
	private function _get_schedules($strict = FALSE)
	{
		$sql = 'SELECT sched.time_from,
					   sched.time_to,
					   sched.day,
					   s.section_name,
					   s.id,
					   r.name,
					   r.description,
					   l.level_desc,
					   concat(e.first_name," ",e.middle_name," ",e.last_name) as adviser,
					   su.subj_desc,
					   su.subj_id as subject_id
				FROM schedule sched
				LEFT JOIN sections s ON sched.section_id = s.id
				LEFT JOIN rooms r ON r.section_id = s.id
				LEFT JOIN levels l ON l.level_id = s.grade_level_id
				LEFT JOIN employees e ON e.emp_id = s.adviser_id
				LEFT JOIN subjects su ON su.subj_id = sched.subject_id
				WHERE teacher_id = ?
				AND s.adviser_id != ?';

		if($strict == TRUE)
		{
			$sql .= ' GROUP BY sched.section_id';
		}


		$query = $this->db->query($sql,array($this->tuid,$this->tuid));
		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	public function get_teacher_advisory_and_students($sec_id,$strict = FALSE)
	{
		
		$sql = "SELECT sch.id,
					   sbj.subj_desc,
					   sbj.subj_id,
					   sbj.subject_name as subj_name
				FROM schedule sch
				LEFT JOIN subjects sbj ON sbj.subj_id = sch.subject_id
				WHERE sch.section_id = ?";

		if($strict !== FALSE)
		{
			$sql .= ' AND teacher_id = ?';
		}

		$q = $this->db->query($sql,[$sec_id,$this->tuid]);
		
		
		// pd($this->db->last_query());
		if($q->num_rows() >= 1)
		{
			// pd($q->result());
			foreach($q->result() as $k => $v)
			{
				$section_subj[$k]['subject'] = $v->subj_desc;
				$section_subj[$k]['subject_name'] = $v->subj_name == NULL ? '' : $this->subj_name;
				$section_subj[$k]['students'] = $this->get_student_grade_by_specific_subject_per_section($sec_id,$v->subj_id);
			}
			
			
			return $section_subj;
		}else{
			return FALSE;
		}
	}


	public function get_teacher_advisory_student_subject($sec_id,$e_id)
	{
		$sql = "SELECT sch.id,
					   sbj.subj_desc,
					   sbj.subj_id,
					   sbj.subject_name as subj_name
				FROM schedule sch
				LEFT JOIN subjects sbj ON sbj.subj_id = sch.subject_id
				LEFT JOIN curriculum cur ON cur.subject_id = sbj.subj_id
				WHERE sch.section_id = ?
				AND teacher_id = ?
				ORDER BY cur.position";
		$q = $this->db->query($sql,[$sec_id,$this->tuid]);
		
		
		// pd($this->db->last_query());
		if($q->num_rows() >= 1)
		{
			// pd($q->result());
			foreach($q->result() as $k => $v)
			{
				$section_subj[$k]['subject'] = $v->subj_desc;
				$section_subj[$k]['subject_name'] = $v->subj_name == NULL ? '' : $this->subj_name;
				$section_subj[$k]['students'] = $this->get_student_grade_by_specific_subject_per_section($sec_id,$v->subj_id);
			}
			
			
			return $section_subj;
		}else{
			return FALSE;
		}
	}
	public function get_teacher_advisory_and_students_2($sec_id)
	{
		$sql = "SELECT sch.id,
					   sbj.subj_desc,
					   sbj.subj_id,
					   sbj.subject_name as subj_name
				FROM schedule sch
				LEFT JOIN subjects sbj ON sbj.subj_id = sch.subject_id
				LEFT JOIN curriculum cur ON cur.subject_id = sbj.subj_id
				WHERE sch.section_id = ?
				AND teacher_id = ?
				ORDER BY cur.position";
		$q = $this->db->query($sql,[$sec_id,$this->tuid]);
		
		// pd($this->db->last_query());
		if($q->num_rows() >= 1)
		{
			foreach($q->result() as $k => $v)
			{
				$section_subj[$k]['subject'] = $v->subj_desc;
				$section_subj[$k]['subject_name'] = $v->subj_name == NULL ? '' : $this->subj_name;
				$section_subj[$k]['students'] = $this->get_student_grade_by_specific_subject_per_section($sec_id,$v->subj_id);
			}
			return $section_subj;
		}else{
			return FALSE;
		}
	}

	public function get_student_grade_by_specific_subject_per_section($section_id,$subject_id)
	{
		$sql = "SELECT e.e_id,
					   concat(p.firstname,' ',p.lastname) as fullname
				FROM enrollments e
				LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
				WHERE e.e_section_id = ?
				AND e.e_ay_id = ?";
		$q = $this->db->query($sql,array($section_id,$this->c_sy->id));
		
		if($q->num_rows() >= 1)
		{	
			foreach($q->result() as $k => $v)
			{
				$data[$k]['student_name'] = $v->fullname;
				$data[$k]['student_e_id'] = $v->e_id;
				$data[$k]['subject'] = $this->_get_subject_and_grade($v->e_id,$subject_id);
				$data[$k]['remarks'] = $this->_get_student_remarks($v->e_id);
			}
			
			return $data;
		}else{
			return FALSE;
		}
	}
	
	public function get_student_subjects($section,$enrollment,$strict = FALSE)
	{
		$get_curriculum = 'SELECT cur.id as cur_id,
								  ss.subject_id,
								  s.subj_code,
	  							s.subj_desc,
	  							s.subj_desc as subject_desc
						   FROM student_subjects ss
						   LEFT JOIN curriculum cur ON cur.subject_id = ss.subject_id
						   LEFT JOIN schedule sc ON sc.subject_id = ss.subject_id
						   LEFT JOIN subjects s ON s.subj_id = ss.subject_id
						   WHERE ss.enrollment_id = ? 
						   group by ss.subject_id';

		if($strict == FALSE)
		{
			$get_curriculum .= ' ORDER BY cur.position';
			$result = $this->db->query($get_curriculum,[$enrollment]);
		}else{
			$get_curriculum .= ' AND teacher_id = ?
			   ORDER BY cur.position';
			$result = $this->db->query($get_curriculum,[$enrollment,$this->tuid]);
		}
						
		if($result)
		{
			foreach ($result->result() as $key => $value) 
			{
				$stud_subk[$key]['name'] = $value->subject_desc;
				$stud_subk[$key]['sub_id'] = $value->subject_id;
				$stud_subk[$key]['grades'] = $this->subject_grades($enrollment,$value->subject_id);
			}

			return isset($stud_subk) ? $stud_subk : FALSE ;
		}else{
			return FALSE;
		}
	}

	/*
	 * Get Subject Grade and Compute Final Remarks
	 */

	public function subject_grades($e_id = '',$subj_id)
	{
		$select = 'SELECT value,id, grading_period_id,subject_id
				   FROM student_grades
				   WHERE enrollment_id = ?
				   AND subject_id = ?';

		$result = $this->db->query($select,[$e_id,$subj_id]);

		$g = $result->num_rows() >=1 ? $result->result() : FALSE;

		$r = false;

		/**
		 * Sort record, set Grading Period ID as key
		 * Compute for average final remarks. (Sum of Grade/number of grading period)
		 *    -> only if all grading periods are present
		 */
		if($g){

			$c_record = 0;
			$c_with_grade = 0;
			$t_grade = 0; //total grade
			$f_final_grade = 0;

			foreach ($g as $key => $v) {
				$r[$v->grading_period_id] = $v;

				$c_record++;

				//check if grade exist
				if(!empty($v->value)){
					$c_with_grade++;
					$t_grade += floatval($v->value);
				}
			}

			if($t_grade && $c_record && $c_with_grade){

				if($c_with_grade == $c_record){
					$f_final_grade = $t_grade / $c_record;
					$r['final'] = round($f_final_grade, 3);
				}
			}
		}

		return $r;
	}

	private function _get_subject_and_grade($eid,$sub_id)
	{
		$select  = 'SELECT * 
					FROM student_grades
					WHERE enrollment_id = ?
					AND ay_id = ?
					AND subject_id = ?';
					
		$q = $this->db->query($select,array($eid,$this->c_sy->id,$sub_id));
		// return $this->db->last_query();
		return $q->num_rows() >= 1 ? $q->result() : NULL;
	}
	
	public function set_student_grades($post = FALSE)
	{
		if($post !== FALSE)
		{
			$post = $post['student'];
			$counter = 0;
			
			foreach($post as $k => $v)
			{
				foreach($v as $kk => $vv)
				{
					$data[$counter]['enrollment_id'] = $k;
					$data[$counter]['ay_id'] = $this->c_sy->id;
					$data[$counter]['value'] = $vv;
					$data[$counter]['id'] = $kk;
					$counter++;
				}
			}

			$this->db->update_batch('student_grades',$data,'id');
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}else{
			return FALSE;
		}	
	}
	
	
	public function get_subject_desc($sub_id)
	{
		$q = $this->db->where('subj_id',$sub_id)->limit(1)->get('subjects');
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	public function get_section_desc($sec_id)
	{
		$sql = "SELECT section_name,
					   max_load,
					   current_load,
					   level_desc,
					   concat(e.first_name,' ',e.last_name) as adviser_name
				FROM sections s
				LEFT JOIN employees e ON s.adviser_id = e.emp_id
				LEFT JOIN levels l ON l.level_id = s.grade_level_id
				WHERE s.id = ?
				";
		$q = $this->db->query($sql,array($sec_id));
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	public function update_student_grade($data,$id)
	{	
		if(is_numeric($data['option'])){ // check numeric grade if possible - lesser or equal to the grading system highest score
			$this->load->model('M_grading_system');
			if(!$this->M_grading_system->check_numeric_grade($data['option'])){ RETURN FALSE; }
		}

		$this->db->set('value',$data['option'])
				 ->where('id',$id)
				 ->limit(1)
				 ->update('student_grades');
		
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	public function del_student_grade($data,$eid)
	{	
		$this->db->where('subject_id', $data);
		$this->db->where('enrollment_id', $eid);
		$this->db->delete('student_subjects'); 

		$this->db->where('subject_id', $data);
		$this->db->where('enrollment_id', $eid);
		$this->db->delete('student_grades'); 
		// vp($data); vp($eid);
		// vd($this->db->last_query());
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}

	public function get_advisory_students_remarks($id ='')
	{
			$sql = "SELECT e.e_id,
						   concat_ws('',pf.lastname,' ',pf.firstname) as fullname,
						   sc.section_name,
						   dob
					FROM enrollments e
					LEFT JOIN sections sc ON e.e_section_id = sc.id
					LEFT JOIN profiles pf ON pf.profile_id = e.e_profile_id
					WHERE sc.adviser_id = ?
					AND sc.id = ?";
					
			$res = $this->db->query($sql,[$this->tuid,$id]);
		
			if($res->num_rows() >=1)
			{
				foreach($res->result() as $k => $v)
				{
					$student_remarks[$k]['remarks'] = $this->_get_student_remarks($v->e_id);
					$student_remarks[$k]['fullname'] = $v->fullname;
					$student_remarks[$k]['enrollment_id'] = $v->e_id;
					$student_remarks[$k]['section_name'] = $v->section_name;
					$student_remarks[$k]['dob'] = $v->dob;
				}
			
				return $student_remarks;
			}else{
				return FALSE;
			}
	}

	public function get_student_remarks_by_enrollment_id($enrollment_id = '')
	{
		$student_remarks[0]['remarks'] = $this->_get_student_remarks($enrollment_id);
		$student_remarks[0]['fullname'] = '';
		$student_remarks[0]['enrollment_id'] = $enrollment_id;
		$student_remarks[0]['section_name'] = '';
		$student_remarks[0]['dob'] = '';
				
		return $student_remarks;
	}
	
	public function _get_all_advisory_sections_for_dd()
	{
		$sql = "SELECT sc.id,
					   sc.section_name
				FROM sections sc
				WHERE sc.adviser_id = ? ";
				
		$res = $this->db->query($sql,[$this->tuid]);
		
		if($res->num_rows() >=1)
		{
			$dp[' '] = ' -- Choose Section -- ';
			foreach($res->result() as $k => $v)
			{
				$dp[$this->hs->encrypt(intval($v->id))] = ucwords(strtolower($v->section_name));
			}
		
			return $dp;
		}else{
			return [' ','-- No Section Advisory Assigned --'];
		}
	
	}
	
	private function _get_student_remarks($e_id)
	{
		$sql = "SELECT id, e_id, remarks, ay_id, gp_id
				FROM student_remarks sr
				WHERE sr.e_id = ?
				ORDER BY sr.gp_id";
		$r = $this->db->query($sql,[$e_id]);
		$i = $r->num_rows() >=1 ? $r->result() : FALSE;

		if(!$i){
			$this->load->model('M_student_grades');
			$this->M_student_grades->generate_remarks($e_id); //Create Remarks Automatically
			$r = $this->db->query($sql,[$e_id,$this->c_sy->id]);
			$i = $r->num_rows() >=1 ? $r->result() : FALSE;
		}

		//sort record make grading period id as key
		$z = false;
		if($i){
			foreach ($i as $k => $v) {
				$z[$v->gp_id] = $v;
			}
		}

		return $z;
	}
	
	public function add_student_remarks($id,$data)
	{
		$this->db->set('remarks',trim(htmlentities($data['option'])))
				 ->set('by',$this->tuid)
				 ->where('id',$id)
				 ->update('student_remarks');
				 
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}

	public function get_advisory_students_attendance($section_id)
	{
		$sql = "SELECT e.e_id,
					   concat_ws('',pf.lastname,' ',pf.firstname) as fullname,
					   sc.section_name,
					   dob
				FROM enrollments e
				LEFT JOIN sections sc ON e.e_section_id = sc.id
				LEFT JOIN profiles pf ON pf.profile_id = e.e_profile_id
				WHERE sc.adviser_id = ?
				AND sc.id = ?";
				
		$res = $this->db->query($sql,[$this->tuid,$section_id]);
	
		if($res->num_rows() >=1)
		{
			foreach($res->result() as $k => $v)
			{
				$student_remarks[$k]['attendance'] = $this->_get_student_attendance($v->e_id);
				$student_remarks[$k]['fullname'] = $v->fullname;
				$student_remarks[$k]['enrollment_id'] = $v->e_id;
				$student_remarks[$k]['dob'] = $v->dob;
			}
			return $student_remarks;
		}else{
			return FALSE;
		}
	}

	private function _get_student_attendance($e_id)
	{
		$sql = 'SELECT * 
				FROM student_attendance
				WHERE e_id = ?
				AND ay_id = ?
				LIMIT 1';
		$res = $this->db->query($sql,[$e_id,$this->c_sy->id]);
		return $res->num_rows() >= 1 ? $res->row() : FALSE;
	}

	public function get_students($sec)
	{
		$studs = $this->get_student_for_section($sec);

		if($studs)
		{
			$_studs[' '] = ' -- Select Students ';
			foreach($studs as $k => $v)
			{
				$_studs[__link($v->eid)] = ucwords(strtolower($v->fullname));
			}
		}else{
			$_studs[' '] = '-- No Students Assigned on this Section--';
		}

		return $_studs;
	}

	public function get_all_my_students()
	{

		$sched = $this->_get_schedules(TRUE);
		$classes = $this->get_class_section_room();
		//get all enrollment id's from section with schedules only
		if($sched)
		{
			foreach($sched as $k => $v)
			{
				$s_studs[$k]['Section']  = $v->section_name;
				$s_studs[$k]['students']  = $this->get_student_for_section($v->id);
			}
		}

		if($classes)
		{
			foreach($classes as $k => $v)
			{
				$c_studs[$k]['Section']  = $v->section_name;
				$c_studs[$k]['students']  = $this->get_student_for_section($v->id);
			}
		}
		

		$students['schedules'] = @$s_studs;
		$students['advisory'] = @$c_studs;

		return($students);
	}

	public function get_student_attendance_detailed($e_id = '')
	{
		$CI =& get_instance();
		$CI->load->model('M_gperiods','mgep');

		$gperiods = $CI->mgep->get_gperiods();

		// pd($gperiods);
		if($gperiods)
		{
			foreach ($gperiods as $key => $value) 
			{	
				$result_set = $this->_get_student_attendance_detailed($value->gp_id,$e_id);

				if(!$result_set){
					$this->load->model('M_student_attendance');
					$this->M_student_attendance->generate_attendance_by_gp($e_id, $value->gp_id); //Create Record Automatically
					$result_set = $this->_get_student_attendance_detailed($value->gp_id,$e_id);
				}
				
				if($result_set !== FALSE)
				{
					$attendance[$value->gp_id] = $result_set; 
					
				}else{

					$attendance[$value->gp_id] = (object)['tardy'=>'','present'=>'','absent'=>'','no_of_days'=>'','id'=>'','generate'=>true];
				}
			}
			
			return ($attendance);
			
		}else{
			return FALSE;
		}
	}


	private function _get_student_attendance_detailed($gp,$eid)
	{
		$q = $this->db->where('e_id',$eid)
						  ->where('gperiod',$gp)
						  ->get('student_attendance_detailed');
		
		$r = $q->num_rows() >=1 ? $q->row() : FALSE;
		return $r;
	}
	
	public function send_commendations($e_id,$options)
	{
		if(!empty($options['student_commendation_commendation']))
		{
			$emp_id = $this->get_employee_id();

			$commendations['employee'] = $emp_id;
			$commendations['ay_id'] = $this->c_sy->id;
			$commendations['gp_id'] = $this->c_gp->gp_id;
			$commendations['message'] = htmlentities($options['student_commendation_commendation']);
			$commendations['sender_id'] = $emp_id;
			$commendations['student_id'] = $e_id;
			$commendations['read'] = 0;
			$commendations['created'] = NOW;
			$commendations['updated'] = NOW;
			$this->db->insert('student_commendations',$commendations);

			return $this->db->affected_rows() >= 1 ? 1 : 0;
			
		}else{
			return 0;
		}
	}
	
	public function get_section_assigned_grade_level($section_id = '')
	{
		
		$section_assigned_level = $this->db->select('grade_level_id')
				 ->where('id',$section_id)
				 ->limit(1)
				 ->get('sections');
		return $section_assigned_level->num_rows() >= 1 ? $section_assigned_level->row()->grade_level_id : FALSE;
	}
	
	public function get_all_sent_commendations($limit,$start)
	{
		$select = 'SELECT concat_ws("",pr.lastname," , ",pr.firstname," ",pr.middlename) as fullname,
						  l.level_desc,
						  concat_ws("",emp.last_name," , ",emp.first_name) as adviser,
						  section_name,
						  sc.id,
						  sc.message
				   FROM student_commendations sc
				   LEFT JOIN enrollments e ON sc.student_id = e.e_id
				   LEFT JOIN levels l ON l.level_id = e.e_grade_level
				   LEFT JOIN profiles pr ON pr.profile_id = e.e_profile_id
				   LEFT JOIN sections s ON s.id = e.e_section_id
				   LEFT JOIN employees emp ON s.adviser_id = emp.emp_id
				   WHERE sc.ay_id =? 
				   AND employee = ? 
				   ORDER BY section_name,pr.lastname 
				   LIMIT '.$start.','.$limit;

		$execute_query = $this->db->query($select,[$this->c_sy->id,$this->tuid]);
		return $execute_query->num_rows() >= 1 ? $execute_query->result() : FALSE;
	}
	
	public function count_sent_commendations()
	{
		return $this->db->where('ay_id',$this->c_sy->id)->where('employee',$this->tuid)->count_all('student_commendations');
	}
}